"""
Shipments API routes - 物流管理
Using raw SQL queries with psycopg2 (NO ORM!)
"""

from flask import Blueprint, request, jsonify
from db.connection import execute_query

bp = Blueprint('shipments', __name__, url_prefix='/api/shipments')

@bp.route('', methods=['GET'])
def get_shipments():
    """
    Get all shipments
    GET /api/shipments
    """
    try:
        query = """
            SELECT s.shipment_id, s.tracking_number, s.ship_date, s.delivery_date,
                   s.status,
                   o.order_id, r.full_name as recipient_name,
                   pc.clinic_name, pc.address as clinic_address
            FROM shipments s
            JOIN orders o ON s.order_id = o.order_id
            JOIN recipients r ON o.recipient_id = r.recipient_id
            LEFT JOIN partner_clinics pc ON o.clinic_id = pc.clinic_id
            ORDER BY s.shipment_id ASC
        """
        
        shipments = execute_query(query)
        
        return jsonify({
            'code': 200,
            'data': shipments,
            'count': len(shipments)
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
                'message': str(e)
            }), 500

@bp.route('/<int:shipment_id>', methods=['GET'])
def get_shipment(shipment_id):
    """
    Get a single shipment by ID
    GET /api/shipments/:id
    """
    try:
        query = """
            SELECT s.*, o.order_id, o.total_price,
                   r.full_name as recipient_name,
                   r.contact_email, r.contact_phone,
                   pc.clinic_name, pc.address as clinic_address
            FROM shipments s
            JOIN orders o ON s.order_id = o.order_id
            JOIN recipients r ON o.recipient_id = r.recipient_id
            LEFT JOIN partner_clinics pc ON o.clinic_id = pc.clinic_id
            WHERE s.shipment_id = %s
        """
        
        shipment = execute_query(query, (shipment_id,), fetch_one=True)
        
        if not shipment:
            return jsonify({
                'code': 404,
                'message': 'Shipment not found'
            }), 404
        
        return jsonify({
            'code': 200,
            'data': shipment
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
                'message': str(e)
            }), 500

@bp.route('', methods=['POST'])
def create_shipment():
    """
    Create a new shipment
    POST /api/shipments
    """
    try:
        data = request.json
        
        required_fields = ['order_id']
        for field in required_fields:
            if field not in data:
                return jsonify({
                    'code': 400,
                    'message': f'Missing required field: {field}'
                }), 400
        
        query = """
            INSERT INTO shipments (order_id, ship_date, delivery_date, tracking_number, status)
            VALUES (%s, %s, %s, %s, %s)
            RETURNING shipment_id, tracking_number, status
        """
        
        params = (
            data['order_id'],
            data.get('ship_date'),
            data.get('delivery_date'),
            data.get('tracking_number'),
            data.get('status', 'Preparing')
        )
        
        result = execute_query(query, params, fetch_one=True)
        
        # Update order status to Shipped
        update_order_query = """
            UPDATE orders
            SET status = 'Shipped'
            WHERE order_id = %s
        """
        execute_query(update_order_query, (data['order_id'],), fetch=False)
        
        return jsonify({
            'code': 200,
            'message': 'Shipment created successfully',
            'data': result
        }), 201
        
    except Exception as e:
        return jsonify({
            'code': 500,
                'message': str(e)
            }), 500

@bp.route('/<int:shipment_id>', methods=['PUT'])
def update_shipment(shipment_id):
    """
    Update a shipment
    PUT /api/shipments/:id
    """
    try:
        data = request.json
        
        update_fields = []
        params = []
        
        allowed_fields = ['status', 'delivery_date', 'tracking_number', 'ship_date']
        
        for field in allowed_fields:
            if field in data:
                update_fields.append(f"{field} = %s")
                params.append(data[field])
        
        if not update_fields:
            return jsonify({
                'code': 400,
                'message': 'No fields to update'
            }), 400
        
        params.append(shipment_id)
        
        query = f"""
            UPDATE shipments
            SET {', '.join(update_fields)}
            WHERE shipment_id = %s
            RETURNING shipment_id, tracking_number, status
        """
        
        result = execute_query(query, tuple(params), fetch_one=True)
        
        if not result:
            return jsonify({
                'code': 404,
                'message': 'Shipment not found'
            }), 404
        
        return jsonify({
            'code': 200,
            'message': 'Shipment updated successfully',
            'data': result
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
                'message': str(e)
            }), 500

@bp.route('/tracking/<tracking_number>', methods=['GET'])
def track_shipment(tracking_number):
    """
    Track a shipment by tracking number
    GET /api/shipments/tracking/:tracking_number
    """
    try:
        query = """
            SELECT s.shipment_id, s.tracking_number, s.ship_date,
                   s.delivery_date, s.status,
                   o.order_id, pc.clinic_name
            FROM shipments s
            JOIN orders o ON s.order_id = o.order_id
            LEFT JOIN partner_clinics pc ON o.clinic_id = pc.clinic_id
            WHERE s.tracking_number = %s
        """
        
        shipment = execute_query(query, (tracking_number,), fetch_one=True)
        
        if not shipment:
            return jsonify({
                'code': 404,
                'message': 'Shipment not found'
            }), 404
        
        return jsonify({
            'code': 200,
            'data': shipment
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
                'message': str(e)
            }), 500

@bp.route('/<int:shipment_id>/deliver', methods=['POST'])
def mark_delivered(shipment_id):
    """
    Mark a shipment as delivered
    POST /api/shipments/:id/deliver
    """
    try:
        query = """
            UPDATE shipments
            SET status = 'Delivered', delivery_date = CURRENT_DATE
            WHERE shipment_id = %s
            RETURNING shipment_id, tracking_number, delivery_date
        """
        
        result = execute_query(query, (shipment_id,), fetch_one=True)
        
        if not result:
            return jsonify({
                'code': 404,
                'message': 'Shipment not found'
            }), 404
        
        # Update order status to Completed
        update_order_query = """
            UPDATE orders
            SET status = 'Completed'
            WHERE order_id = (SELECT order_id FROM shipments WHERE shipment_id = %s)
        """
        execute_query(update_order_query, (shipment_id,), fetch=False)
        
        return jsonify({
            'code': 200,
            'message': 'Shipment marked as delivered',
            'data': result
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
                'message': str(e)
            }), 500

